package com.ucode.hrm.controller.service;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.IdcardUtil;
import cn.hutool.core.util.ObjUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.log.Log;
import cn.hutool.log.LogFactory;
import com.ucode.hrm.controller.dto.HrmExcelCompareResult;
import com.ucode.hrm.controller.dto.HrmExcelCustomerContact;
import com.ucode.hrm.controller.dto.HrmExcelEmployee;
import com.ucode.hrm.handle.EmployeeCostCalculator;
import com.ucode.hrm.service.*;
import com.ucode.model.hrm.*;
import com.ucode.model.hrm.convert.HrmEmployeeContractConvert;
import com.ucode.model.hrm.convert.HrmEmployeeCostDetailConvert;
import com.ucode.model.hrm.enums.HrmCooperationStatusEnum;
import com.ucode.model.hrm.enums.HrmCustomerProjectStatusEnum;
import com.ucode.model.hrm.enums.HrmEmployeeContractStatusEnum;
import com.ucode.model.hrm.enums.HrmEmploymentTypeEnum;
import com.ucode.model.sys.user.AdminUser;
import com.ucode.ssm.user.service.AdminUserService;
import com.ucode.tool.base.enums.GlobalResultCodeEnum;
import com.ucode.tool.base.enums.SexEnum;
import com.ucode.tool.exception.UcodeServiceException;
import com.ucode.tool.util.IdentifyUtils;
import com.ucode.tool.util.JsonUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.ZoneId;
import java.util.*;

/**
 * Excel 处理 服务
 * @Author: liliang
 * @Date: 2025/6/29 19:49
 * @Version: 1.0
 */
@Service
public class ApiHrmExcelHandleService {

    private static final Log log = LogFactory.get();

    @Resource
    private HrmCustomerCompanyService hrmCustomerCompanyService;
    @Resource
    private HrmCustomerProjectService hrmCustomerProjectService;
    @Resource
    private HrmEmployeeService hrmEmployeeService;
    @Resource
    private HrmEmployeeCompensationService hrmEmployeeCompensationService;
    @Resource
    private HrmContractTplService hrmContractTplService;
    @Resource
    private HrmCompanyService companyService;
    @Resource
    private HrmEmployeeContractService employeeContractService;
    @Resource
    private AdminUserService adminUserService;
    @Resource
    private HrmSocialSecurityRuleService hrmSocialSecurityRuleService;
    @Resource
    private HrmEmployeeCostDetailService hrmEmployeeCostDetailService;
    @Resource
    private HrmPaySlipService hrmPaySlipService;
    /**
     * 导入员工数据
     * @param file
     */
    public void  importEmployeesData(MultipartFile file) throws Exception {
        List<HrmExcelEmployee> employees = parseExcelEmployees(file);

        Map<String, HrmCustomerCompany> customerCompanyMap = new HashMap<>();
        Map<String, HrmCustomerProject> customerProjectMap = new HashMap<>();

        HrmCompany company = companyService.validate(1940381662922752000L);
        int index = 1;
        for(HrmExcelEmployee employee : employees){

            log.info("开始处理员工数据："+ JsonUtils.toJsonString( employee)+",当前第"+index+"条");

            if(StrUtil.isBlank(employee.getSuperiorUnit()) || StrUtil.isBlank(employee.getEmploymentUnit())){
                log.info("数据不完整："+ JsonUtils.toJsonString( employee));
                continue;
            }

            //保存客户
            HrmCustomerCompany customerCompany = customerCompanyMap.get(employee.getSuperiorUnit().trim());
            if(ObjUtil.isNull(customerCompany)){
                customerCompany = hrmCustomerCompanyService.queryOneByName(employee.getSuperiorUnit().trim());
                if(ObjUtil.isNull(customerCompany)) {
                    customerCompany = new HrmCustomerCompany();
                    customerCompany.setCompanyName(employee.getSuperiorUnit().trim());
                    customerCompany.setStatus(HrmCooperationStatusEnum.IN_PROGRESS.getCode());
                    customerCompany.setId(hrmCustomerCompanyService.insert(customerCompany));
                }

                customerCompanyMap.put(customerCompany.getCompanyName(),customerCompany);
            }

            //保存项目
            HrmCustomerProject customerProject = customerProjectMap.get(customerCompany.getId()+employee.getEmploymentUnit().trim());
            if(ObjUtil.isNull(customerProject)){
                customerProject = hrmCustomerProjectService.queryOneByName(customerCompany.getId(),employee.getEmploymentUnit().trim());
                if(ObjUtil.isNull(customerProject)) {
                    customerProject = new HrmCustomerProject();
                    customerProject.setCustomerCompanyId(customerCompany.getId());
                    customerProject.setProjectName(employee.getEmploymentUnit().trim());
                    customerProject.setStatus(HrmCustomerProjectStatusEnum.ONGOING.getCode());
                    customerProject.setOwnerAdminId(1L);
                    customerProject.setId(hrmCustomerProjectService.insert(customerProject));
                }

                customerCompanyMap.put(customerCompany.getCompanyName(),customerCompany);
            }

            //保存员工
            HrmEmployee hrmEmployee = new HrmEmployee();
            hrmEmployee.setId(IdentifyUtils.getDefaultSnowflakeId());
            hrmEmployee.setRealName(employee.getName());

            hrmEmployee.setGender(IdcardUtil.getGenderByIdCard(employee.getIdNumber()) == 1 ? SexEnum.MALE.getCode() : SexEnum.FEMALE.getCode());
            hrmEmployee.setEthnicity(employee.getEthnicity());
            //if(ObjUtil.isNotNull(employee.getBirthDate())){
            //    hrmEmployee.setBirthDate(employee.getBirthDate().toInstant()
            //            .atZone(ZoneId.systemDefault())
            //            .toLocalDate());
            //}

            Map<String,Integer> EducationMap = new HashMap<>();
            EducationMap.put("初中及以下",1);
            EducationMap.put("高中/中专",2);
            EducationMap.put("大专",3);
            EducationMap.put("本科",4);
            EducationMap.put("硕士",5);
            EducationMap.put("博士",6);
            EducationMap.put("其他",10);
            if(StrUtil.isNotBlank(employee.getEducation())){
                hrmEmployee.setEducation(EducationMap.get(employee.getEducation()));
            }

            Map<String,Integer> maritalStatusMap = new HashMap<>();
            maritalStatusMap.put("未婚",1);
            maritalStatusMap.put("已婚",2);
            maritalStatusMap.put("离异",3);
            maritalStatusMap.put("丧偶",4);
            if(StrUtil.isNotBlank(employee.getMaritalStatus())){
                hrmEmployee.setMaritalStatus(maritalStatusMap.get(employee.getMaritalStatus()));
            }

            hrmEmployee.setHouseholdType(employee.getHouseholdType());

            //根据身份证号码获取籍贯
            hrmEmployee.setRegisteredResidence(IdcardUtil.getProvinceByIdCard(employee.getIdNumber()));
            hrmEmployee.setDetailedAddress(employee.getRegisteredAddress());

            hrmEmployee.setCnontactPhone(employee.getEmergencyContact());
            hrmEmployee.setIdCardNumber(employee.getIdNumber());
            //从身份证中获取出生日期
            hrmEmployee.setBirthDate(IdcardUtil.getBirthDate(employee.getIdNumber()).toLocalDateTime().toLocalDate());


            hrmEmployee.setPhone(employee.getPersonalPhone());
            hrmEmployee.setEmail(employee.getEmail());
            if(ObjUtil.isNotNull(employee.getEntryDate())){
                hrmEmployee.setHireDate(employee.getEntryDate().toInstant()
                        .atZone(ZoneId.systemDefault())
                        .toLocalDate());
            }

            if(ObjUtil.isNotNull(employee.getProbationEndDate())){
                hrmEmployee.setTrialEndDate(employee.getProbationEndDate().toInstant()
                        .atZone(ZoneId.systemDefault())
                        .toLocalDate());
            }

            if(ObjUtil.isNotNull(employee.getContractEndDate())){
                hrmEmployee.setContractEndDate(employee.getContractEndDate().toInstant()
                        .atZone(ZoneId.systemDefault())
                        .toLocalDate());
            }

            //离职日期与离职状态
            if(ObjUtil.isNotNull(employee.getResignationDate())){
                hrmEmployee.setResignationDate(cn.hutool.core.date.DateUtil.toLocalDateTime(employee.getResignationDate()).toLocalDate());
            }

            if(ObjUtil.isNotNull(employee.getResignationDate())){
                hrmEmployee.setEmploymentType(HrmEmploymentTypeEnum.LEFT.getCode());
            }else if(ObjUtil.isNotNull(hrmEmployee.getTrialEndDate()) && hrmEmployee.getTrialEndDate().isAfter(LocalDate.now())){
                hrmEmployee.setEmploymentType(HrmEmploymentTypeEnum.PROBATION.getCode());
            }else {
                hrmEmployee.setEmploymentType(HrmEmploymentTypeEnum.FULL_TIME.getCode());
            }
            //hrmEmployee.setEmploymentType(ObjUtil.isNotNull(employee.getResignationDate()) ? HrmEmploymentTypeEnum.LEFT.getCode() : HrmEmploymentTypeEnum.FULL_TIME.getCode());

            hrmEmployee.setCompanyId(customerCompany.getId());
            hrmEmployee.setProjectId(customerProject.getId());
            hrmEmployee.setCustomCompany(employee.getEmploymentUnit());
            hrmEmployee.setDepartmentName(employee.getRecruitmentStatus());//这里excel没有部门名称，使用上级部门名称代替
            hrmEmployee.setPositionName(employee.getJobTitle());
            hrmEmployee.setWorkLocation(employee.getArea());
            if(ObjUtil.isNotNull(employee.getRenewalCount()) || employee.getRenewalCount() > 0){
                hrmEmployee.setContractType(2);
            }else {
                hrmEmployee.setContractType(1);
            }

            //合同终止
            if (hrmEmployee.getEmploymentType() == HrmEmploymentTypeEnum.LEFT.getCode()) {
                hrmEmployee.setContractStatus(HrmEmployeeContractStatusEnum.TERMINATED.getCode());
            }else if(ObjUtil.isNotNull(employee.getContractEndDate()) && employee.getContractEndDate().before(new Date())){
                //合同到期
                hrmEmployee.setContractStatus(HrmEmployeeContractStatusEnum.EXPIRED.getCode());
            }else {
                hrmEmployee.setContractStatus(HrmEmployeeContractStatusEnum.COMPLETED_SIGNED.getCode());
            }

            try {
                hrmEmployeeService.insert(hrmEmployee);
            }catch (Exception e) {
                //这里添加失败的，基本上身份证号重复了
                continue;
            }


            //添加员工薪酬
            HrmEmployeeCompensation hrmEmployeeCompensation = new HrmEmployeeCompensation();
            hrmEmployeeCompensation.setId(hrmEmployee.getId());
            hrmEmployeeCompensation.setBankName(employee.getBank());
            hrmEmployeeCompensation.setBankCard(employee.getBankAccount());
            hrmEmployeeCompensation.setBaseSalary(BigDecimal.valueOf(employee.getBasicSalary()));
            hrmEmployeeCompensation.setSocialSecurityDate(hrmEmployee.getHireDate());
            hrmEmployeeCompensation.setSocialSecurityRuleId(1937900357991596032L);//默认社保规则
            hrmEmployeeCompensation.setHousingFundBase(BigDecimal.ZERO);
            hrmEmployeeCompensation.setHousingFundEnterpriseRate(BigDecimal.ZERO);
            hrmEmployeeCompensation.setHousingFundPersonalRate(BigDecimal.ZERO);

            hrmEmployeeCompensationService.insert(hrmEmployeeCompensation);

            //未离职,且合同已到期
            if(ObjUtil.equals(HrmEmploymentTypeEnum.FULL_TIME.getCode(),hrmEmployee.getEmploymentType()) &&
                    ObjUtil.equals(HrmEmployeeContractStatusEnum.EXPIRED.getCode(),hrmEmployee.getContractStatus()) &&
                ObjUtil.isNotNull(employee.getContractXqEndDate())){
                Long contractTplId = 0L;
                if("否".equals(employee.getSocialSecurityLocation())) {
                    //无社保合同模板
                    contractTplId = 1938246218395222024L;
                }else {
                    contractTplId = 1938246218395222025L;
                }

                HrmContractTpl contractTpl = hrmContractTplService.selectByPrimaryKey(contractTplId);
                HrmEmployeeContract employeeContract = HrmEmployeeContractConvert.INSTANCE.build(hrmEmployee,hrmEmployeeCompensation,company,contractTpl);
                employeeContract.setContractType(2);
                employeeContract.setStartDate(hrmEmployee.getContractEndDate());
                employeeContract.setEndDate(cn.hutool.core.date.DateUtil.toLocalDateTime(employee.getContractXqEndDate()).toLocalDate());
                employeeContract.setTrialEndDate(null);

                employeeContractService.insert(employeeContract);
            }

            index++;
        }

    }

    /**
     * 解析Excel文件并返回员工列表
     * @param file
     * @return
     * @throws IOException
     */
    public List<HrmExcelEmployee> parseExcelEmployees(MultipartFile file) throws IOException {
        List<HrmExcelEmployee> employees = new ArrayList<>(); // 创建空列表存储结果
        Workbook workbook = WorkbookFactory.create(file.getInputStream()); // 从输入流创建工作簿

        //第一个工作表：在职员工  第二个工作表：离职员工
        for (int sheetIndex = 0; sheetIndex <= 1; sheetIndex++) {
            Sheet sheet = null;
            try {
               sheet = workbook.getSheetAt(sheetIndex); // 获取工作表
            }catch (Exception e){
                //跳过不存在的工作表
                continue;
            }

            // 遍历工作表的每一行（从第1行开始，跳过标题行）

            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i); // 获取当前行
                if (row == null) continue; // 跳过空行

                HrmExcelEmployee employee = new HrmExcelEmployee(); // 创建新员工对象

                // 解析每个单元格并设置到员工对象中
                // 索引对应Excel列：0=A列, 1=B列, 2=C列...
                employee.setSerialNumber(getIntValue(row.getCell(0))); // A列: 序号
                employee.setName(getStringValue(row.getCell(1))); // B列: 姓名
                employee.setNature(getStringValue(row.getCell(2))); // C列: 性质
                employee.setSuperiorUnit(getStringValue(row.getCell(3))); // D列: 上级单位
                employee.setEmploymentUnit(getStringValue(row.getCell(4))); // E列: 用工单位
                employee.setRecruitmentStatus(getStringValue(row.getCell(5))); // F列: 招聘情况
                employee.setArea(getStringValue(row.getCell(6))); // G列: 地区
                employee.setPosition(getStringValue(row.getCell(7))); // H列: 岗位
                employee.setBasicSalary(getDoubleValue(row.getCell(8))); // I列: 基本工资
                employee.setGender(getStringValue(row.getCell(9))); // J列: 性别
                employee.setEmploymentStatus(getStringValue(row.getCell(10))); // K列: 在职状态
                employee.setEntryDate(getDateValue(row.getCell(11))); // L列: 入职日期
                employee.setContractSignMethod(getStringValue(row.getCell(12))); // M列: 合同签署小程序/线下
                employee.setContractExpiryReminder(getStringValue(row.getCell(13))); // N列: 合同到期提醒
                employee.setRenewalCount(getIntValue(row.getCell(14))); // O列: 续签次数
                employee.setResignationDate(getDateValue(row.getCell(15))); // P列: 离职日期
                employee.setEmploymentDays(getIntValue(row.getCell(16))); // Q列: 在职天数
                employee.setContractSigned(getStringValue(row.getCell(17))); // R列: 合同
                employee.setSocialSecurityLocation(getStringValue(row.getCell(18))); // S列: 社保公积金缴纳地
                employee.setJobTitle(getStringValue(row.getCell(19))); // T列: 职位
                employee.setIdNumber(getStringValue(row.getCell(20))); // U列: 身份证号
                employee.setBirthDate(getDateValue(row.getCell(21))); // V列: 出生日期
                employee.setEducation(getStringValue(row.getCell(22))); // W列: 学历
                employee.setEthnicity(getStringValue(row.getCell(23))); // X列: 民族
                employee.setMaritalStatus(getStringValue(row.getCell(24))); // Y列: 婚姻状况
                employee.setHouseholdType(getStringValue(row.getCell(25))); // Z列: 户口性质
                employee.setRegisteredAddress(getStringValue(row.getCell(26))); // AA列: 户籍所在地地址
                employee.setCurrentAddress(getStringValue(row.getCell(27))); // AB列: 现居住地地址
                employee.setEmail(getStringValue(row.getCell(28))); // AC列: 邮箱
                employee.setPersonalPhone(getStringValue(row.getCell(29))); // AD列: 本人联系方法
                employee.setEmergencyContact(getStringValue(row.getCell(30))); // AE列: 第一紧急联系人电话
                employee.setProbationPeriod(getIntValue(row.getCell(31))); // AF列: 试用期
                employee.setProbationEndDate(getDateValue(row.getCell(32))); // AG列: 试用期到期日
                employee.setContractTerm(getIntValue(row.getCell(33))); // AH列: 合同期
                employee.setContractEndDate(getDateValue(row.getCell(34))); // AI列: 合同到期日
                employee.setAge(getIntValue(row.getCell(35))); // AJ列: 年龄
                employee.setIdCopy(getStringValue(row.getCell(36))); // AK列: 身份证复印件
                employee.setMedicalReport(getStringValue(row.getCell(37))); // AL列: 体检报告
                employee.setEducationCertificate(getStringValue(row.getCell(38))); // AM列: 学历证明
                employee.setResignationCertificate(getStringValue(row.getCell(39))); // AN列: 离职证明
                employee.setPhoto(getStringValue(row.getCell(40))); // AO列: 相片
                employee.setBank(getStringValue(row.getCell(41))); // AP列: 银行
                employee.setBankAccount(getStringValue(row.getCell(42))); // AQ列: 银行卡号
                employee.setOtherBankAccount(getStringValue(row.getCell(43))); // AR列: 其他银行卡号
                employee.setEmployerLiabilityInsurance(getStringValue(row.getCell(44)));// AS列: 雇主责任险
                employee.setContractXqEndDate(getDateValue(row.getCell(45)));// AS列: 合同到期续期截止日期
                // 可根据需要继续解析剩余列（AT到AU列）...

                employees.add(employee); // 将员工对象添加到结果列表
            }

        }

        workbook.close(); // 关闭工作簿释放资源

        log.info("解析完成，共有员工数据：{}条", employees.size());
        return employees;
    }


    /**
     * 导入客户数据
     * @param file
     */
    public void  importCustomerContactsData(MultipartFile file) throws Exception {
        List<HrmExcelCustomerContact> list = parseExcelCustomerContacts(file);

        if(CollUtil.isNotEmpty(list)){
            Map<String, HrmCustomerCompany> customerCompanyMap = new HashMap<>();
            Map<String, HrmCustomerProject> customerProjectMap = new HashMap<>();
            Map<String, AdminUser> adminUserMap = new HashMap<>();

            int index = 1;
            for(HrmExcelCustomerContact bean : list){

                log.info("开始处理客户数据："+ JsonUtils.toJsonString( bean)+",当前第"+index+"条");

                //保存客户
                HrmCustomerCompany customerCompany = customerCompanyMap.get(bean.getSuperiorUnit().trim());
                if(ObjUtil.isNull(customerCompany)){
                    customerCompany = hrmCustomerCompanyService.queryOneByName(bean.getSuperiorUnit().trim());
                    if(ObjUtil.isNull(customerCompany)) {
                        customerCompany = new HrmCustomerCompany();
                        customerCompany.setCompanyName(bean.getSuperiorUnit().trim());
                        customerCompany.setContact(bean.getClientContact());
                        customerCompany.setMobile(bean.getContactPhone());
                        customerCompany.setAddress(bean.getAddress());
                        customerCompany.setStatus(HrmCooperationStatusEnum.IN_PROGRESS.getCode());
                        customerCompany.setId(hrmCustomerCompanyService.insert(customerCompany));
                    }else {
                        customerCompany.setContact(bean.getClientContact());
                        customerCompany.setMobile(bean.getContactPhone());
                        customerCompany.setAddress(bean.getAddress());
                        hrmCustomerCompanyService.update(customerCompany);
                    }

                    customerCompanyMap.put(customerCompany.getCompanyName(),customerCompany);
                }

                //保存项目
                HrmCustomerProject customerProject = customerProjectMap.get(customerCompany.getId()+bean.getProjectName().trim());
                if(ObjUtil.isNull(customerProject)){
                    customerProject = hrmCustomerProjectService.queryOneByName(customerCompany.getId(),bean.getProjectName().trim());
                    if(ObjUtil.isNull(customerProject)) {
                        customerProject = new HrmCustomerProject();
                        customerProject.setCustomerCompanyId(customerCompany.getId());
                        customerProject.setProjectName(bean.getProjectName().trim());
                        if(ObjUtil.isNotNull(bean.getStartDate())){
                            customerProject.setStartDate(cn.hutool.core.date.DateUtil.toLocalDateTime(bean.getStartDate()));
                        }

                        if(ObjUtil.isNotNull(bean.getExpireDate())){
                            customerProject.setEndDate(cn.hutool.core.date.DateUtil.toLocalDateTime(bean.getExpireDate()));
                        }

                        customerProject.setStatus(HrmCustomerProjectStatusEnum.ONGOING.getCode());

                        customerProject.setOwnerAdminId(1L);

                        if(StrUtil.isNotBlank(bean.getManagerPhone())){

                            AdminUser adminUser = adminUserMap.get(bean.getManagerPhone().trim());
                            if(ObjUtil.isNull(adminUser)) {
                                adminUser = adminUserService.getUserByMobile(bean.getManagerPhone().trim());
                                if(ObjUtil.isNotNull(adminUser)){
                                    adminUserMap.put(bean.getManagerPhone().trim(),adminUser);
                                }
                            }

                            if(ObjUtil.isNotNull(adminUser)) {
                                customerProject.setOwnerAdminId(adminUser.getId());
                            }
                        }

                        customerProject.setId(hrmCustomerProjectService.insert(customerProject));
                    }else {
                        if(ObjUtil.isNotNull(bean.getStartDate())){
                            customerProject.setStartDate(cn.hutool.core.date.DateUtil.toLocalDateTime(bean.getStartDate()));
                        }

                        if(ObjUtil.isNotNull(bean.getExpireDate())){
                            customerProject.setEndDate(cn.hutool.core.date.DateUtil.toLocalDateTime(bean.getExpireDate()));
                        }

                        if(StrUtil.isNotBlank(bean.getManagerPhone())){
                            log.info("没走这里吗？："+ bean.getManagerPhone());
                            AdminUser adminUser = adminUserMap.get(bean.getManagerPhone().trim());
                            if(ObjUtil.isNull(adminUser)) {
                                adminUser = adminUserService.getUserByMobile(bean.getManagerPhone().trim());
                                if(ObjUtil.isNotNull(adminUser)){
                                    adminUserMap.put(bean.getManagerPhone().trim(),adminUser);
                                }
                            }

                            if(ObjUtil.isNotNull(adminUser)) {
                                customerProject.setOwnerAdminId(adminUser.getId());
                                log.info("没走这里3吗？："+ bean.getManagerPhone());
                            }
                        }

                        hrmCustomerProjectService.update(customerProject);
                    }

                    customerCompanyMap.put(customerCompany.getCompanyName(),customerCompany);
                }

                index++;
            }
        }


    }
    /**
     * 解析Excel文件并返回客户通信录列表
     * @param file
     * @return
     * @throws IOException
     */
    public List<HrmExcelCustomerContact> parseExcelCustomerContacts(MultipartFile file) throws IOException {
        List<HrmExcelCustomerContact> contacts = new ArrayList<>();
        InputStream inputStream = file.getInputStream();
        Workbook workbook = WorkbookFactory.create(inputStream);

        Sheet sheet = workbook.getSheetAt(0);

        // 从第二行开始解析（跳过标题行）
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;

            HrmExcelCustomerContact contact = new HrmExcelCustomerContact();

            // 序号
            contact.setId(getIntValue(row.getCell(0)));

            // 上级单位
            contact.setSuperiorUnit(getStringValue(row.getCell(1)));

            // 项目名称
            contact.setProjectName(getStringValue(row.getCell(2)));

            // 日期处理
            contact.setStartDate(getDateValue(row.getCell(3)));
            contact.setExpireDate(getDateValue(row.getCell(4)));

            // 经理信息
            contact.setProjectManager(getStringValue(row.getCell(5)));
            contact.setManagerPhone(getStringValue(row.getCell(6)));

            // 客户信息
            contact.setClientContact(getStringValue(row.getCell(7)));
            contact.setContactPhone(getStringValue(row.getCell(8)));
            contact.setContactEmail(getStringValue(row.getCell(9)));

            // 收件地址
            contact.setAddress(getStringValue(row.getCell(10)));

            contacts.add(contact);
        }
        return contacts;
    }


    /**
     * 解析Excel文件并返回员工费用列表
     * @param file
     * @return
     * @throws IOException
     */
    public List<HrmEmployeeCostDetail> importCostDetail(MultipartFile file) throws IOException {
        List<HrmEmployeeCostDetail> list = new ArrayList<>();
        InputStream inputStream = file.getInputStream();
        Workbook workbook = WorkbookFactory.create(inputStream);

        String batchnum = ""+IdentifyUtils.getDefaultSnowflakeId();
        Sheet sheet = workbook.getSheetAt(0);

        Map<Long, HrmSocialSecurityRule> hrmSocialSecurityRuleMap = new HashMap<>();

        log.info("费用导入->总行数："+ sheet.getLastRowNum());
        // 从第四行开始解析（跳过标题行）
        for (int i = 3; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;

            // 身份证号
            String IdCard = getStringValue(row.getCell(3));

            log.info("费用导入->身份证："+ IdCard);

            if(StrUtil.isBlank(IdCard)){
                log.info("费用导入->缺少身份证号，第："+ i+"行");
                continue;
            }

            //查询员工薪酬表
            HrmEmployee employee = hrmEmployeeService.getByIdCardNumber(IdCard);
            if(ObjUtil.isNull(employee)){
                log.info("费用导入->根据身份证未查询到员工："+ IdCard);
                continue;
            }


            HrmEmployeeCompensation hrmEmployeeCompensation = hrmEmployeeCompensationService.selectByPrimaryKey(employee.getId());
            if(ObjUtil.isNull(hrmEmployeeCompensation)){
                log.info("费用导入->员工未配置薪酬："+ employee.getId());
                continue;
            }

            HrmSocialSecurityRule hrmSocialSecurityRule = hrmSocialSecurityRuleMap.get(hrmEmployeeCompensation.getSocialSecurityRuleId());
            if(ObjUtil.isNull(hrmSocialSecurityRule)) {
                hrmSocialSecurityRule = hrmSocialSecurityRuleService.selectByPrimaryKey(hrmEmployeeCompensation.getSocialSecurityRuleId());
                if(ObjUtil.isNotNull(hrmSocialSecurityRule)) {
                    hrmSocialSecurityRuleMap.put(hrmSocialSecurityRule.getId(),hrmSocialSecurityRule);
                }
            }
            if(ObjUtil.isNull(hrmSocialSecurityRule)){
                log.info("费用导入->未找到社保规则："+ hrmEmployeeCompensation.getSocialSecurityRuleId());
                continue;
            }

            HrmEmployeeCostDetail bean = HrmEmployeeCostDetailConvert.INSTANCE.convert(hrmEmployeeCompensation, hrmSocialSecurityRule);
            bean.setIdCard(IdCard);
            bean.setEntryDate(employee.getHireDate());
            bean.setEmployeeName(employee.getRealName());
            bean.setEmployeeId(employee.getId());
            bean.setCompanyId(employee.getCompanyId());
            bean.setProjectId(employee.getProjectId());

            // 入职时间
            Date entryDate = getDateValue(row.getCell(4));
            if(ObjUtil.isNotNull(entryDate)) {
                bean.setEntryDate(entryDate.toInstant()
                        .atZone(ZoneId.systemDefault())
                        .toLocalDate());
            }

            // 在职地区
            bean.setWorkArea(getStringValue(row.getCell(5)));

            // 费用月份
            log.info("费用导入->费用月份："+ row.getCell(6));
            Date feeMonth = getDateValue(row.getCell(6));
            if(ObjUtil.isNotNull(feeMonth)) {
                bean.setFeeMonth(feeMonth.toInstant()
                        .atZone(ZoneId.systemDefault())
                        .toLocalDate());
            }

            //月数
            bean.setMonths(getIntValue(row.getCell(7)));

            // 差异津贴
            bean.setRegionalDifference(BigDecimal.valueOf(getDoubleValue(row.getCell(36))));

            //专业技术津贴
            bean.setProfessionalAllowance(BigDecimal.valueOf(getDoubleValue(row.getCell(40))));

            //综合补贴
            bean.setComprehensiveSubsidy(BigDecimal.valueOf(getDoubleValue(row.getCell(41))));

            //工资补发
            bean.setSalaryReissue(BigDecimal.valueOf(getDoubleValue(row.getCell(42))));

            //报销金额
            bean.setReimbursement(BigDecimal.valueOf(getDoubleValue(row.getCell(43))));

            //提成金额
            bean.setCommission(BigDecimal.valueOf(getDoubleValue(row.getCell(44))));

            //应发合计
            bean.setTotalPayable(BigDecimal.valueOf(getDoubleValue(row.getCell(45))));

            //服务费
            bean.setServiceFee(BigDecimal.valueOf(getDoubleValue(row.getCell(51))));

            //节假日福利
            bean.setHolidayBenefits(BigDecimal.valueOf(getDoubleValue(row.getCell(52))));

            //计算
            EmployeeCostCalculator.calculate(bean);

            bean.setId(IdentifyUtils.getDefaultSnowflakeId());
            bean.setBatchnum(batchnum);
            bean.setCostType(1);
            list.add(bean);
        }

        hrmEmployeeCostDetailService.batchInsert(list);
        return list;
    }

    /**
     * 解析Excel文件并返回员工工资条列表
     * @param file
     * @return
     * @throws IOException
     */
    public List<HrmPaySlip> importPaySlipData(MultipartFile file) throws IOException {
        List<HrmPaySlip> records = new ArrayList<>();
        InputStream is = file.getInputStream();
        Workbook workbook = WorkbookFactory.create(is);
        Sheet sheet = workbook.getSheetAt(0);
        Row headerRow = sheet.getRow(1);//获取标题行

        String batchnum = ""+IdentifyUtils.getDefaultSnowflakeId();
        // 获取动态列标题（第6列开始）
        List<String> dynamicHeaders = new ArrayList<>();
        for (int i = 5; i < headerRow.getLastCellNum(); i++) {
            Cell cell = headerRow.getCell(i);
            if(StrUtil.isBlank(cell.getStringCellValue())){
                break;
            }
            dynamicHeaders.add(cell.getStringCellValue());
        }

        log.info("员工工资条导入->动态列标题："+ JsonUtils.toJsonString(dynamicHeaders));

        // 处理数据行
        for (int rowIndex = 2; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) continue;

            HrmPaySlip bean = new HrmPaySlip();
            // 解析固定列
            bean.setEmployeeName(getStringValue(row.getCell(0)));
            bean.setIdCard(getStringValue(row.getCell(1)));


            log.info("工资条导入->身份证："+ bean.getIdCard());
            if(StrUtil.isBlank(bean.getIdCard())){
                log.info("工资条导入->缺少身份证号，第："+ rowIndex+"行");
                continue;
            }
            HrmEmployee employee = hrmEmployeeService.getByIdCardNumber(bean.getIdCard());
            if(ObjUtil.isNull(employee)){
                log.info("工资条导入->根据身份证未查询到员工："+ bean.getIdCard());
                continue;
            }

            Date feeMonth = getDateValue(row.getCell(2));
            if(ObjUtil.isNotNull(feeMonth)) {
                bean.setFeeMonth(feeMonth.toInstant()
                        .atZone(ZoneId.systemDefault())
                        .toLocalDate());
            }
            bean.setTotalPayable(new BigDecimal(getDoubleValue(row.getCell(3))));
            bean.setNetSalary(new BigDecimal(getDoubleValue(row.getCell(4))));
            // 解析动态列为JSON
            Map<String, Object> details = new LinkedHashMap<>();
            for (int i = 0; i < dynamicHeaders.size(); i++) {
                int colIndex = 5 + i;
                Cell cell = row.getCell(colIndex);

                if (cell != null) {
                    switch (cell.getCellType()) {
                        case STRING:
                            details.put(dynamicHeaders.get(i), cell.getStringCellValue());
                            break;
                        case NUMERIC:
                            details.put(dynamicHeaders.get(i), cell.getNumericCellValue());
                            break;
                        case BOOLEAN:
                            details.put(dynamicHeaders.get(i), cell.getBooleanCellValue());
                            break;
                        default:
                            details.put(dynamicHeaders.get(i), "");
                    }
                } else {
                    details.put(dynamicHeaders.get(i), "");
                }
            }

            bean.setDetailInfo(JsonUtils.toJsonString(details));

            bean.setVisible(false);
            bean.setCostId(0L);

            bean.setId(IdentifyUtils.getDefaultSnowflakeId());
            bean.setBatchnum(batchnum);
            bean.setEmployeeId(employee.getId());
            bean.setCompanyId(employee.getCompanyId());
            bean.setProjectId(employee.getProjectId());
            records.add(bean);
        }

        if(CollUtil.isNotEmpty( records)){
            hrmPaySlipService.batchInsert(records);
        }

        return records;
    }

    /**
     * 比较Excel文件中的两个Sheet页，找出差异并生成结果文件
     *
     * @param file 上传的Excel文件
     * @param keyColumnIndex 作为唯一键的列索引（从0开始）
     * @return 包含差异结果的Excel文件
     * @throws IOException 当文件读取或写入发生错误时抛出
     */
    public HrmExcelCompareResult compareExcelSheets(MultipartFile file, int keyColumnIndex) throws IOException {
        if (file.isEmpty()) {
            throw new UcodeServiceException(GlobalResultCodeEnum.VALIDATE_FAILED.getCode(),"上传的文件不能为空");
        }

        // 使用try-with-resources确保资源正确释放
        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = WorkbookFactory.create(inputStream)) {

            // 验证Excel文件是否包含至少两个Sheet页
            if (workbook.getNumberOfSheets() < 2) {
                throw new UcodeServiceException(GlobalResultCodeEnum.VALIDATE_FAILED.getCode(),"Excel文件必须包含至少两个Sheet页");
            }

            //获取两个Sheet页
            Sheet sheet1 = workbook.getSheetAt(0);
            Sheet sheet2 = workbook.getSheetAt(1);

            //验证关键列索引是否有效
            if (keyColumnIndex < 0) {
                throw new UcodeServiceException(GlobalResultCodeEnum.VALIDATE_FAILED.getCode(),"关键列索引不能为负数");
            }

            //构建基于唯一键的数据映射（每行数据根据关键列的值存储）
            Map<Object, Row> sheet1Data = buildRowMap(sheet1, keyColumnIndex);
            Map<Object, Row> sheet2Data = buildRowMap(sheet2, keyColumnIndex);

            // 比较两个Sheet的数据并找出差异
            List<DiffResult> diffResults = compareData(sheet1Data, sheet2Data, keyColumnIndex);

            log.info("完成对比，开始输出对比结果");
            // 创建包含差异结果的Excel工作簿
            byte[] resultContent = createDiffWorkbook(diffResults, sheet1, keyColumnIndex);

            return new HrmExcelCompareResult("Excel对比差异结果.xlsx", resultContent);
        }
    }

    /**
     * 将Sheet中的数据行按关键列的值建立映射
     *
     * @param sheet 要处理的Sheet页
     * @param keyColumnIndex 关键列索引
     * @return 以关键列值为键，行数据为值的映射
     */
    private Map<Object, Row> buildRowMap(Sheet sheet, int keyColumnIndex) {
        Map<Object, Row> rowMap = new HashMap<>();

        // 遍历Sheet中的每一行
        for (Row row : sheet) {
            // 跳过标题行（假设第一行是标题）
            if (row.getRowNum() == 0) continue;

            // 获取关键列的单元格
            Cell keyCell = row.getCell(keyColumnIndex);
            // 如果关键单元格不为空
            if (keyCell != null) {
                // 获取关键单元格的值
                Object key = getStringValue(keyCell);
                if (rowMap.containsKey(key)) {
                    log.info("发现重复的关键值: " + key + " 在行 " + (row.getRowNum() + 1));
                    continue;
                    //throw new UcodeServiceException(GlobalResultCodeEnum.VALIDATE_FAILED.getCode(),"发现重复的关键值: " + key + " 在行 " + (row.getRowNum() + 1));
                }
                // 将行数据存入映射
                rowMap.put(key, row);
            }
        }
        return rowMap;
    }


    /**
     * 比较两个Sheet的数据并找出差异
     *
     * @param sheet1Data 第一个Sheet的数据映射
     * @param sheet2Data 第二个Sheet的数据映射
     * @param keyColumnIndex 关键列索引
     * @return 差异结果列表
     */
    private List<DiffResult> compareData(Map<Object, Row> sheet1Data, Map<Object, Row> sheet2Data, int keyColumnIndex) {
        List<DiffResult> results = new ArrayList<>();

        // 获取所有唯一键的并集（两个Sheet中所有关键值的集合）
        Set<Object> allKeys = new HashSet<>();
        allKeys.addAll(sheet1Data.keySet());
        allKeys.addAll(sheet2Data.keySet());

        log.info("开始对比：" + allKeys);
        // 遍历所有关键值
        for (Object key : allKeys) {
            log.info("逐行对比：" + key);
            // 从两个Sheet中获取对应关键值的行
            Row row1 = sheet1Data.get(key);
            Row row2 = sheet2Data.get(key);

            // 情况1: 关键值只在第一个Sheet中存在
            if (row1 != null && row2 == null) {
                results.add(new DiffResult(key, row1, null, true, Collections.emptyList()));
            }
            // 情况2: 关键值只在第二个Sheet中存在
            else if (row1 == null && row2 != null) {
                results.add(new DiffResult(key, null, row2, true, Collections.emptyList()));
            }
            // 情况3: 关键值在两个Sheet中都存在，需要比较内容
            else {
                // 比较两行的非关键列
                List<Integer> diffColumns = compareRows(row1, row2, keyColumnIndex);
                if (!diffColumns.isEmpty()) {
                    // 如果有差异列，添加到结果中
                    results.add(new DiffResult(key, row1, row2, false, diffColumns));
                }
            }
        }

        return results;
    }

    /**
     * 比较两行的内容，找出差异列的索引
     *
     * @param row1 第一行
     * @param row2 第二行
     * @param keyColumnIndex 关键列索引（跳过比较）
     * @return 差异列的索引列表
     */
    private List<Integer> compareRows(Row row1, Row row2, int keyColumnIndex) {
        List<Integer> diffColumns = new ArrayList<>();
        // 获取两行中最大的列数
        int maxCells = Math.max(row1.getLastCellNum(), row2.getLastCellNum());

        log.info("开始对比单元格内容");
        // 遍历所有列
        for (int i = 0; i < maxCells; i++) {

            // 跳过关键列
            if (i == keyColumnIndex) continue;

            // 获取两行当前列的单元格
            Cell cell1 = row1.getCell(i);
            Cell cell2 = row2.getCell(i);

            // 比较单元格内容
            if (cellsDiffer(cell1, cell2)) {
                // 如果不同，记录列索引
                diffColumns.add(i);
            }
        }

        return diffColumns;
    }

    /**
     * 比较两个单元格的内容是否不同
     *
     * @param cell1 第一个单元格
     * @param cell2 第二个单元格
     * @return 如果不同返回true，否则返回false
     */
    private boolean cellsDiffer(Cell cell1, Cell cell2) {
        // 两个单元格都为null视为相同
        if (cell1 == null && cell2 == null) {
            return false;
        }
        // 只有一个为null视为不同
        if (cell1 == null || cell2 == null) {
            return true;
        }

        // 如果单元格类型不同，视为不同
        if (cell1.getCellType() != cell2.getCellType()) {
            return true;
        }

        // 根据具体类型比较内容
        switch (cell1.getCellType()) {
            case STRING:
                // 字符串类型比较内容
                return !cell1.getStringCellValue().equals(cell2.getStringCellValue());
            case NUMERIC:
                // 如果是日期格式，比较日期值
                if (DateUtil.isCellDateFormatted(cell1) || DateUtil.isCellDateFormatted(cell2)) {
                    try {
                        return !cell1.getDateCellValue().equals(cell2.getDateCellValue());
                    } catch (Exception e) {
                        return true;
                    }
                }
                // 否则比较数值
                return cell1.getNumericCellValue() != cell2.getNumericCellValue();
            case BOOLEAN:
                // 比较布尔值
                return cell1.getBooleanCellValue() != cell2.getBooleanCellValue();
            case FORMULA:
                // 比较公式
                return !cell1.getCellFormula().equals(cell2.getCellFormula());
            case BLANK:
                // 空白单元格比较类型
                return cell2.getCellType() != CellType.BLANK;
            default:
                // 其他情况视为不同
                return true;
        }
    }


    /**
     * 创建包含差异结果的Excel工作簿（优化版）
     *
     * @param diffResults 差异结果列表
     * @param originalSheet 原始Sheet（用于获取表头）
     * @param keyColumnIndex 关键列索引
     * @return 包含差异结果的Excel文件的字节数组
     * @throws IOException 当写入发生错误时抛出
     */
    private byte[] createDiffWorkbook(List<DiffResult> diffResults, Sheet originalSheet, int keyColumnIndex)
            throws IOException {

        // 使用SXSSFWorkbook处理大文件（流式写入）
        try (SXSSFWorkbook resultWorkbook = new SXSSFWorkbook(100)) { // 保持100行在内存中
            // 正确设置 Unicode 支持的方式：
            // 1. 确保字体支持 Unicode
            Font defaultFont = resultWorkbook.createFont();
            defaultFont.setCharSet(Font.DEFAULT_CHARSET); // 使用默认字符集（通常支持 Unicode）

            Sheet resultSheet = resultWorkbook.createSheet("差异结果");

            // 1. 样式预创建（避免重复创建）
            CellStyle diffStyle = createDiffCellStyle(resultWorkbook);
            CellStyle headerStyle = createHeaderCellStyle(resultWorkbook);

            // 2. 批量处理表头
            processHeaderRow(originalSheet, resultSheet, headerStyle);

            // 3. 批量处理差异数据
            processDiffRows(diffResults, resultSheet, keyColumnIndex, diffStyle);

            // 4. 自动调整列宽（优化性能，只调整非空列）
            autoSizeColumns(resultSheet);

            // 5. 流式写入输出
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            resultWorkbook.write(outputStream);
            resultWorkbook.dispose(); // 清理临时文件

            return outputStream.toByteArray();
        }
    }

    // 辅助方法：创建差异单元格样式
    private CellStyle createDiffCellStyle(SXSSFWorkbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(IndexedColors.RED.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 创建支持 Unicode 的字体
        Font font = workbook.createFont();
        font.setCharSet(Font.DEFAULT_CHARSET); // 关键设置
        style.setFont(font);

        return style;
    }

    // 辅助方法：创建表头样式
    private CellStyle createHeaderCellStyle(SXSSFWorkbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        style.setFont(font);
        return style;
    }

    // 辅助方法：处理表头行
    private void processHeaderRow(Sheet originalSheet, Sheet resultSheet, CellStyle headerStyle) {
        Row headerRow = originalSheet.getRow(0);
        if (headerRow != null) {
            Row resultHeaderRow = resultSheet.createRow(0);

            // 使用迭代器减少方法调用
            Iterator<Cell> cellIterator = headerRow.cellIterator();
            while (cellIterator.hasNext()) {
                Cell srcCell = cellIterator.next();
                Cell destCell = resultHeaderRow.createCell(srcCell.getColumnIndex());
                destCell.setCellValue(srcCell.getStringCellValue());
                destCell.setCellStyle(headerStyle);
            }

            // 添加状态列
            Cell statusHeader = resultHeaderRow.createCell(headerRow.getLastCellNum());
            statusHeader.setCellValue("差异状态");
            statusHeader.setCellStyle(headerStyle);
        }
    }

    // 辅助方法：处理差异数据行
    private void processDiffRows(List<DiffResult> diffResults, Sheet resultSheet,
                                 int keyColumnIndex, CellStyle diffStyle) {
        // 使用批量处理减少行创建开销
        for (DiffResult result : diffResults) {
            Row resultRow = resultSheet.createRow(resultSheet.getLastRowNum() + 1);
            Row baseRow = result.row1 != null ? result.row1 : result.row2;

            // 预计算最后单元格索引
            int lastCellNum = baseRow.getLastCellNum();

            // 批量复制单元格
            copyRowData(baseRow, resultRow, keyColumnIndex,
                    result.entireRowMissing, result.diffColumns, diffStyle);

            // 添加状态说明
            addStatusCell(resultRow, lastCellNum, result);
        }
    }

    // 辅助方法：复制行数据
    private void copyRowData(Row srcRow, Row destRow, int keyColumnIndex,
                             boolean entireRowMissing, List<Integer> diffColumns,
                             CellStyle diffStyle) {
        // 使用最有效的遍历方式
        for (int i = 0; i < srcRow.getLastCellNum(); i++) {
            Cell srcCell = srcRow.getCell(i);
            if (srcCell != null) {
                Cell destCell = destRow.createCell(i);
                copyCellValue(srcCell, destCell);

                // 标记差异列（跳过关键列）
                if (i != keyColumnIndex &&
                        (entireRowMissing || (diffColumns != null && diffColumns.contains(i)))) {
                    destCell.setCellStyle(diffStyle);
                }
            }
        }
    }

    // 辅助方法：优化单元格值复制
    private void copyCellValue(Cell srcCell, Cell destCell) {
        switch (srcCell.getCellType()) {
            case STRING:
                destCell.setCellValue(srcCell.getStringCellValue());
                break;
            case NUMERIC:
                destCell.setCellValue(srcCell.getNumericCellValue());
                break;
            case BOOLEAN:
                destCell.setCellValue(srcCell.getBooleanCellValue());
                break;
            case FORMULA:
                destCell.setCellFormula(srcCell.getCellFormula());
                break;
            default:
                destCell.setBlank();
        }
    }

    // 辅助方法：添加状态单元格
    private void addStatusCell(Row row, int columnIndex, DiffResult result) {
        Cell statusCell = row.createCell(columnIndex);
        if (result.entireRowMissing) {
            statusCell.setCellValue(result.row1 != null ? "仅存在于Sheet1" : "仅存在于Sheet2");
        } else {
            statusCell.setCellValue("内容不一致");
        }
    }

    // 辅助方法：优化列宽自动调整
    private void autoSizeColumns(Sheet sheet) {
        if (sheet.getRow(0) != null) {
            int columnCount = sheet.getRow(0).getLastCellNum();
            for (int i = 0; i < columnCount; i++) {
                sheet.autoSizeColumn(i);
                // 设置最小宽度防止过窄
                sheet.setColumnWidth(i, Math.max(sheet.getColumnWidth(i), 3000));
            }
        }
    }

    ///**
    // * 创建包含差异结果的Excel工作簿
    // *
    // * @param diffResults 差异结果列表
    // * @param originalSheet 原始Sheet（用于获取表头）
    // * @param keyColumnIndex 关键列索引
    // * @return 包含差异结果的Excel文件的字节数组
    // * @throws IOException 当写入发生错误时抛出
    // */
    //private byte[] createDiffWorkbook(List<DiffResult> diffResults, Sheet originalSheet, int keyColumnIndex) throws IOException {
    //    log.info("开始创建差异文件excel");
    //    // 创建新的工作簿（XLSX格式）
    //    try (Workbook resultWorkbook = new XSSFWorkbook()) {
    //        // 创建结果Sheet页
    //        Sheet resultSheet = resultWorkbook.createSheet("差异结果");
    //
    //        // 创建差异单元格样式（红色背景）
    //        CellStyle diffStyle = resultWorkbook.createCellStyle();
    //        diffStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
    //        diffStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    //
    //        // 创建表头样式（加粗）
    //        CellStyle headerStyle = resultWorkbook.createCellStyle();
    //        Font headerFont = resultWorkbook.createFont();
    //        headerFont.setBold(true);
    //        headerStyle.setFont(headerFont);
    //
    //        // 复制原始表头并添加状态列
    //        Row headerRow = originalSheet.getRow(0);
    //        if (headerRow != null) {
    //            Row resultHeaderRow = resultSheet.createRow(0);
    //
    //            // 复制原始表头
    //            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
    //                Cell srcCell = headerRow.getCell(i);
    //                if (srcCell != null) {
    //                    Cell destCell = resultHeaderRow.createCell(i);
    //                    destCell.setCellValue(srcCell.getStringCellValue());
    //                    destCell.setCellStyle(headerStyle);
    //                }
    //            }
    //
    //            // 添加状态列
    //            Cell statusHeader = resultHeaderRow.createCell(headerRow.getLastCellNum());
    //            statusHeader.setCellValue("差异状态");
    //            statusHeader.setCellStyle(headerStyle);
    //        }
    //
    //        // 添加差异结果数据行
    //        for (DiffResult result : diffResults) {
    //            // 创建新行
    //            Row resultRow = resultSheet.createRow(resultSheet.getLastRowNum() + 1);
    //
    //            // 确定使用哪个行作为基础（优先使用第一个Sheet的行）
    //            Row baseRow = result.row1 != null ? result.row1 : result.row2;
    //            int lastCellNum = baseRow.getLastCellNum();
    //
    //            // 复制整行数据
    //            for (int i = 0; i < lastCellNum; i++) {
    //                Cell srcCell = baseRow.getCell(i);
    //                if (srcCell != null) {
    //                    // 创建目标单元格并复制内容和样式
    //                    Cell destCell = resultRow.createCell(i);
    //                    copyCell(srcCell, destCell, resultWorkbook);
    //
    //                    // 标记差异列（跳过关键列）
    //                    if (i != keyColumnIndex &&
    //                            ((result.entireRowMissing) ||
    //                                    (result.diffColumns != null && result.diffColumns.contains(i)))) {
    //                        destCell.setCellStyle(diffStyle);
    //                    }
    //                }
    //            }
    //
    //            // 添加状态说明
    //            Cell statusCell = resultRow.createCell(lastCellNum);
    //            if (result.entireRowMissing) {
    //                statusCell.setCellValue(result.row1 != null ? "仅存在于Sheet1" : "仅存在于Sheet2");
    //            } else {
    //                statusCell.setCellValue("内容不一致");
    //            }
    //        }
    //
    //        // 自动调整列宽
    //        for (int i = 0; i < resultSheet.getRow(0).getLastCellNum(); i++) {
    //            resultSheet.autoSizeColumn(i);
    //        }
    //
    //        // 将工作簿写入字节数组输出流
    //        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    //        resultWorkbook.write(outputStream);
    //        return outputStream.toByteArray();
    //    }
    //}
    /**
     * 复制单元格内容和样式
     *
     * @param srcCell 源单元格
     * @param destCell 目标单元格
     * @param destWorkbook 目标工作簿
     */
    private void copyCell(Cell srcCell, Cell destCell, Workbook destWorkbook) {
        // 创建新样式并复制源样式
        CellStyle style = destWorkbook.createCellStyle();
        style.cloneStyleFrom(srcCell.getCellStyle());
        destCell.setCellStyle(style);

        // 根据源单元格类型复制内容
        switch (srcCell.getCellType()) {
            case STRING:
                destCell.setCellValue(srcCell.getStringCellValue());
                break;
            case NUMERIC:
                destCell.setCellValue(srcCell.getNumericCellValue());
                break;
            case BOOLEAN:
                destCell.setCellValue(srcCell.getBooleanCellValue());
                break;
            case FORMULA:
                destCell.setCellFormula(srcCell.getCellFormula());
                break;
            case BLANK:
                destCell.setBlank();
                break;
            default:
                destCell.setCellValue("");
        }
    }

    /**
     * 差异结果内部类，用于存储比较结果
     */
    private static class DiffResult {
        Object key;                 // 关键列的值
        Row row1;                   // Sheet1中的行
        Row row2;                   // Sheet2中的行
        boolean entireRowMissing;    // 是否整行缺失（只存在于一个Sheet中）
        List<Integer> diffColumns;   // 差异列的索引列表

        DiffResult(Object key, Row row1, Row row2, boolean entireRowMissing, List<Integer> diffColumns) {
            this.key = key;
            this.row1 = row1;
            this.row2 = row2;
            this.entireRowMissing = entireRowMissing;
            this.diffColumns = diffColumns;
        }
    }


    // 获取单元格整数值
    private Integer getIntValue(Cell cell) {
        if (cell == null) return null; // 空单元格返回null

        try {
            // 尝试获取数字值并转为整数
            return (int) cell.getNumericCellValue();
        } catch (Exception e) {
            // 转换失败时尝试从字符串解析
            try {
                return Integer.parseInt(getStringValue(cell));
            } catch (NumberFormatException ex) {
                return null; // 解析失败返回null
            }
        }
    }

    // 获取单元格浮点数值
    private Double getDoubleValue(Cell cell) {
        if (cell == null) return 0.00; // 空单元格返回null

        try {
            // 尝试获取数字值
            return cell.getNumericCellValue();
        } catch (Exception e) {
            // 转换失败时尝试从字符串解析
            try {
                return Double.parseDouble(getStringValue(cell));
            } catch (NumberFormatException ex) {
                log.error(e,"浮点数值转换异常："+ e.getMessage());
                return 0.00; // 解析失败返回null
            }
        }
    }

    // 获取单元格日期值
    private Date getDateValue(Cell cell) {
        if (cell == null) return null;

        try {
            // 1. 优先使用Excel原生日期格式
            if (DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue();
            }
        } catch (Exception e) {
            log.error("Excel日期格式解析异常：{}", e.getMessage());
        }

        try {
            // 2. 尝试从字符串解析
            String strValue = getStringValue(cell);
            if (StrUtil.isBlank(strValue)) return null;

            log.debug("尝试解析日期字符串: {}", strValue);

            // 日期格式自动检测（支持多种格式）
            return parseDateFlexibly(strValue);
        } catch (Exception e) {
            log.error("日期解析失败：原始值=[{}] 原因：{}",
                    cell.toString(), e.getMessage());
            return null;
        }
    }

    // 支持多种格式的日期解析
    private Date parseDateFlexibly(String dateStr) throws ParseException {
        // 常见日期格式模板
        String[] patterns = {
                "yyyy-MM-dd", "yyyy/MM/dd", "yyyyMMdd",
                "dd-MM-yyyy", "dd/MM/yyyy",
                "MM-dd-yyyy", "MM/dd/yyyy",
                "yyyy-MM-dd HH:mm:ss", "yyyy/MM/dd HH:mm:ss",
                "yyyy-MM-dd'T'HH:mm:ss", "yyyyMMdd HHmmss"
        };

        // 首先进行基本格式验证
        if (!isValidDateFormat(dateStr)) {
            throw new ParseException("非法日期格式", 0);
        }

        // 多格式尝试解析
        for (String pattern : patterns) {
            try {
                SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                sdf.setLenient(false); // 严格模式防止错误解析

                // 尝试解析前过滤无效字符
                String cleanDate = dateStr.trim()
                        .replaceAll("\\s+", " ")  // 合并多余空格
                        .replaceAll("[^0-9a-zA-Z :/\\-T]", ""); // 保留有效字符

                Date date = sdf.parse(cleanDate);

                // 解析成功进行二次验证
                if (isValidDate(cleanDate, pattern)) {
                    return date;
                }
            } catch (ParseException ignore) {
                // 当前格式不匹配，继续尝试下一种格式
            }
        }
        throw new ParseException("未匹配到已知格式：" + dateStr, 0);
    }

    // 日期格式验证（防止纯数字解析为日期）
    private boolean isValidDateFormat(String input) {
        // 需要至少包含日期分隔符或字母
        return input.matches(".*[\\-/.:年\\s].*") ||
                input.matches("\\d{4}\\d{2}\\d{2}") || // 纯数字需要是8位
                input.matches("\\d{1,2}\\D{1,2}\\d{1,4}.+"); // 包含分隔符的格式
    }

    // 解析结果二次验证（防止有效格式错误解析）
    private boolean isValidDate(String dateStr, String pattern) {
        try {
            SimpleDateFormat sdf = new SimpleDateFormat(pattern);
            Date parsed = sdf.parse(dateStr);
            return sdf.format(parsed).equals(dateStr); // 来回格式化验证一致性
        } catch (Exception e) {
            return false;
        }
    }


    // 获取单元格字符串值
    private String getStringValue(Cell cell) {
        if (cell == null) return null; // 空单元格返回null

        switch (cell.getCellType()) {
            case STRING: // 文本类型
                return cell.getStringCellValue().trim(); // 返回去除空格的文本
            case NUMERIC: // 数字类型
                if (DateUtil.isCellDateFormatted(cell)) { // 检查是否是日期格式
                    // 日期格式转为"yyyy-MM-dd"字符串
                    return new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue());
                }
                // 普通数字转为字符串（避免科学计数法）
                return String.valueOf((long) cell.getNumericCellValue());
            case BOOLEAN: // 布尔类型
                return String.valueOf(cell.getBooleanCellValue()); // 转为字符串
            case FORMULA: // 公式类型
                // 获取公式计算结果
                return cell.getCellFormula();
            default: // 其他类型
                return ""; // 返回空字符串
        }
    }

}
